/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eproject_sem2;

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author ZER0
 */
public class Employee_sever {

    String user = ConnectSQL.user;
    String pass = ConnectSQL.pass;
    String ulr =ConnectSQL.ulr;
    String DriverClass =ConnectSQL.DriverClass;
    Connection con = null;

    public boolean InsertPrice(String id, String name, String ad, int pn, String dt, String table, String cn1, String cn2, String cn3, String cn4, String cn5) {
        boolean rsx = false;
        try {
            // add du lieu vao database
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "INSERT INTO " + table + "(" + cn1 + "," + cn2 + "," + cn3 + "," + cn4 + "," + cn5 + ") VALUES('" + id + "', '" + name + "', '" + ad + "', " + pn + ", '" + dt + "')";
            PreparedStatement Psttm = con.prepareStatement(sql);
            Psttm.executeUpdate();
            rsx = true;
        } catch (SQLException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //kiem tra ham co gia tri de add ko ,neu ko co thi dong ket noi
            if (con != null) {
                try {
                    con.close();
                    System.out.println("Dong ket noi");
                } catch (Exception e) {
                }
            }
        }
        return rsx;
    }

    public boolean UpdatePrice(String id, String name, String ad, int pn, String dt, String table, String cn1, String cn2, String cn3, String cn4, String cn5) {
        boolean kq = true;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "update " + table + " set  " + cn2 + "= '" + name + "', " + cn3 + "= '" + ad + "', " + cn4 + " =" + pn + ", " + cn5 + "= '" + dt + "' where  " + cn1 + "='" + id + "'";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.executeUpdate();
            kq = false;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return kq;
    }

    public int CheckIDPrice(String id, String table) throws ClassNotFoundException, SQLException {
        int kq = -1;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString(2);
                if (k1.equalsIgnoreCase(id)) {
                    kq = 1;
                    break;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public void ShowTable(String t1, String t2, String t3, String t4, String t5, String table, JTable sa, String i) {
        try {
            //khai bao model de hien thi bang
            DefaultTableModel listtableModel = new DefaultTableModel();
            Vector tableR1 = new Vector();
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                if (i != "All") {
                    if (rs.getString(6).equalsIgnoreCase(i)) {
                        Vector r = new Vector();
                        r.add(rs.getString(2));
                        r.add(rs.getString(3));
                        r.add(rs.getString(4));
                        r.add(rs.getInt(5));
                        r.add(rs.getString(6));
                        tableR1.add(r);
                    }
                } else {
                    Vector r = new Vector();
                    r.add(rs.getString(2));
                    r.add(rs.getString(3));
                    r.add(rs.getString(4));
                    r.add(rs.getInt(5));
                    r.add(rs.getString(6));
                    tableR1.add(r);
                }
            }
            //goi mo do hien thi thong tin ra bang
            Vector<String> columnNames = new Vector<String>();
            columnNames.addElement("" + t1 + "");
            columnNames.addElement("" + t2 + "");
            columnNames.addElement("" + t3 + "");
            columnNames.addElement("" + t4 + "");
            columnNames.addElement("" + t5 + "");
            sa.setModel(listtableModel);
            listtableModel.setDataVector(tableR1, columnNames);
        } catch (SQLException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void ShowTableEdit(String id, String name, float price, JTable sa, String t1, String t2, String t3) {
        //khai bao model de hien thi bang
        DefaultTableModel listTableModel = new DefaultTableModel();
        Vector r = new Vector();
        Vector tableR1 = new Vector();
        r.add(id);
        r.add(name);
        r.add(price);
        tableR1.add(r);
        //goi mo do hien thi thong tin ra bang
        Vector<String> columnNames = new Vector<String>();
        columnNames.addElement("" + t1 + "");
        columnNames.addElement("" + t2 + "");
        columnNames.addElement("" + t3 + "");
        sa.setModel(listTableModel);
        listTableModel.setDataVector(tableR1, columnNames);
    }

    public void Remove(String table, String id, String cn1) {
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "DELETE FROM " + table + " WHERE " + cn1 + "='" + id + "'";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ListOfPrice_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
